import mysql.connector
import json

# 创建与数据库的连接
cnx = mysql.connector.connect(
    host="192.168.80.190",
    port=3306,
    user="root",
    password="12345678",
    database="gaokao",
    use_pure=True,
    ssl_disabled=True
)

# 创建一个游标对象
cursor = cnx.cursor()

# 定义要执行的 SQL 插入语句

with open('json/majorDetail.json', 'r', encoding='utf-8') as f:
    data = json.load(f)
with open('json/majorDetailRest.json', 'r', encoding='utf-8') as f:
    data_rest = json.load(f)

for key, value in data.items():
    insert_query = "INSERT INTO majorDetails VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
    insert_data = ( key, 
                    value.get('name', '未知'), 
                    value.get('hot', '0'), 
                    value.get('year', '未知'), 
                    value.get('degree', '未知'),
                    value.get('is_what', '未知'),
                    value.get('learn_what', '未知'), 
                    value.get('do_what', '未知'), 
                    value.get('content', '未知'), 
                    value.get('recommend_subject', '未知'), 
                    value.get('postgraduate', '未知'), 
                    value.get('subject', '未知'), 
                    value.get('people', '未知'), 
                    data_rest.get(key).get('综合满意度', '0'), 
                    data_rest.get(key).get('办学条件满意度', '0'), 
                    data_rest.get(key).get('教学质量满意度', '0'), 
                    data_rest.get(key).get('就业满意度', '0'),
                    str(value.get('salary_self', [0, 0, 0, 0, 0])), 
                    str(value.get('salary_average', [0, 0, 0, 0, 0])), 
                    value.get('Rate1', '0'), value.get('Rate2', '0'), value.get('Rate3', '0'), value.get('Rate4', '0'), value.get('Rate5', '0'), value.get('Rate6', '0'), 
                    value.get('Job1', '0'), value.get('Job2', '0'), value.get('Job3', '0'), value.get('Job4', '0'), value.get('Job5', '0'), value.get('Job6', '0'), 
                    value.get('rate1', '0'), value.get('rate2', '0'), value.get('rate3', '0'), value.get('rate4', '0'), value.get('rate5', '0'), 
                    value.get('region1', '0'), value.get('region2', '0'), value.get('region3', '0'), value.get('region4', '0'), value.get('region5', '0'),
                    value.get('men_rate', '0:0').split(':')[0])
    cursor.execute(insert_query, insert_data)
    cnx.commit()

# 关闭游标和数据库连接
cursor.close()
cnx.close()
